import mysql.connector
from dotenv import load_dotenv
import os
import hashlib

# 加载环境变量
load_dotenv()

# 数据库配置
db_config = {
    'host': os.getenv('DB_HOST', 'localhost'),
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', '123456'),
}

def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()

def init_db():
    # 连接MySQL服务器
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()
    
    # 创建数据库
    db_name = os.getenv('DB_NAME', 'volunteer_db')
    cursor.execute(f"DROP DATABASE IF EXISTS {db_name}")
    cursor.execute(f"CREATE DATABASE {db_name}")
    cursor.execute(f"USE {db_name}")
    
    # 创建用户表
    cursor.execute('''
        CREATE TABLE users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            user_type ENUM('admin', 'volunteer', 'charity') NOT NULL,
            status ENUM('pending', 'active', 'suspended') DEFAULT 'pending',
            description TEXT,
            contact VARCHAR(100),
            phone VARCHAR(20),
            address TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建活动表
    cursor.execute('''
        CREATE TABLE activities (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            description TEXT NOT NULL,
            start_time DATETIME NOT NULL,
            end_time DATETIME NOT NULL,
            location VARCHAR(200) NOT NULL,
            max_participants INT NOT NULL,
            required_skills TEXT,
            notes TEXT,
            charity_id INT NOT NULL,
            status ENUM('pending', 'approved', 'rejected', 'completed', 'cancelled') DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (charity_id) REFERENCES users(id)
        )
    ''')
    
    # 创建活动报名表
    cursor.execute('''
        CREATE TABLE registrations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            activity_id INT NOT NULL,
            volunteer_id INT NOT NULL,
            status ENUM('pending', 'approved', 'rejected', 'completed') DEFAULT 'pending',
            feedback TEXT,
            rating INT CHECK (rating >= 1 AND rating <= 5),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (activity_id) REFERENCES activities(id),
            FOREIGN KEY (volunteer_id) REFERENCES users(id)
        )
    ''')
    
    # 创建消息表
    cursor.execute('''
        CREATE TABLE messages (
            id INT AUTO_INCREMENT PRIMARY KEY,
            sender_id INT NOT NULL,
            receiver_id INT NOT NULL,
            message_type ENUM('system', 'activity', 'registration') NOT NULL,
            content TEXT NOT NULL,
            is_read BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (sender_id) REFERENCES users(id),
            FOREIGN KEY (receiver_id) REFERENCES users(id)
        )
    ''')
    
    # 创建默认管理员账户
    admin_password = hash_password('admin123')
    cursor.execute('''
        INSERT INTO users (name, email, password, user_type, status)
        VALUES ('Admin', 'admin@example.com', %s, 'admin', 'active')
    ''', (admin_password,))
    
    # 创建示例慈善机构账户
    charity_password = hash_password('charity123')
    cursor.execute('''
        INSERT INTO users (name, email, password, user_type, status, description, contact, phone, address)
        VALUES ('示例慈善机构', 'charity@example.com', %s, 'charity', 'active',
                '这是一个示例慈善机构，致力于帮助有需要的人。', '张三', '13800138000', '北京市朝阳区xxx街道')
    ''', (charity_password,))
    
    # 创建示例志愿者账户
    volunteer_password = hash_password('volunteer123')
    cursor.execute('''
        INSERT INTO users (name, email, password, user_type, status)
        VALUES ('示例志愿者', 'volunteer@example.com', %s, 'volunteer', 'active')
    ''', (volunteer_password,))
    
    cnx.commit()
    cursor.close()
    cnx.close()
    
    print("数据库初始化完成！")
    print("\n默认账户信息：")
    print("管理员 - Email: admin@example.com, 密码: admin123")
    print("机构 - Email: charity@example.com, 密码: charity123")
    print("志愿者 - Email: volunteer@example.com, 密码: volunteer123")

if __name__ == '__main__':
    init_db() 